﻿-- Get all new symbols for which yahoo stuff has to be downloaded
UPDATE	sym
SET		  sym_yahoo_cd = CONCAT(sym_cd, yho_exc_ext)
		, sym_yahoo_fg = 1
		, sym_google_fg = 0
FROM	fin.eod_symbols sym
		INNER JOIN fin.eod_exchanges exc 
			ON (sym_exc_id = exc_id)
		INNER JOIN (SELECT	  exc_cd AS yho_exc_cd
							, CASE exc_cd 
								WHEN 'AMS' THEN '.AS'
								WHEN 'ASX' THEN '.AX'
								WHEN 'BRU' THEN '.BR'
								WHEN 'HKEX' THEN '.HK'
								WHEN 'LIS' THEN '.LS'
								WHEN 'LSE' THEN '.L'
								WHEN 'MLSE' THEN '.MI'
								WHEN 'NZX' THEN '.NZ'
								WHEN 'PAR' THEN '.PA'
								WHEN 'SGX' THEN '.SI'
								WHEN 'TSK' THEN '.TO'
								WHEN 'TSXV' THEN '.V'
								ELSE NULL
							  END as yho_exc_ext
					FROM	fin.eod_exchanges) a 
			ON (exc_cd = a.yho_exc_cd)
WHERE	sym_yahoo_cd IS NULL 
		AND sym_yahoo_fg IS NULL
		AND exc_cd IN ('NYSE', 'NZX', 'AMEX', 'ASX', 'HKEX', 'LSE', 'MLSE', 'PAR', 'LIS', 'TSX', 'TSXV', 'AMS', 'BRU', 'NASDAQ', 'SGX');

-- Count quotes by day to check whether data is complete
SELECT		  exc_cd
			, qot_trade_dt
			, count(qot_id)
FROM		fin.eod_quotes
			INNER JOIN fin.eod_symbols 
				ON (qot_sym_id = sym_id)
			INNER JOIN fin.eod_exchanges 
				ON (sym_exc_id = exc_id)
WHERE		exc_id = 19
			AND qot_cat_id = 7
GROUP BY	exc_cd, qot_trade_dt
ORDER B Y	qot_trade_dt DESC;

